import sys
import os
import logging
import re
import json
from typing import Dict, List, Any, Optional, Tuple

# 添加项目根路径到 sys.path
sys.path.append(os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', '..'))

# 导入 MySQLUtil
from shared.utils.MySQLUtil import MySQLUtil

# 设置日志
logger = logging.getLogger(__name__)

def get_就业省分布(
    project_id: int,
    questionnaire_ids: List[int],
    product_code: Optional[str] = None,
    project_code: Optional[str] = None,
    region_code: Optional[str] = None,
    education: Optional[str] = None
) -> Dict[str, Any]:
    """
    就业省分布 - 指标计算函数
    
    ## 指标说明
    该指标用于统计毕业生就业省份的分布情况，计算各省份就业人数及其占总就业人数的比例。
    数据来源为学生客观表中的就业省份字段(job_province)，仅统计不为空的记录。
    可根据需要按学历进行筛选，学历枚举值包括：本科毕业生、专科毕业生、硕士研究生、博士研究生。
    当education为空时，返回所有学历的明细数据及总体数据。
    
    ## Args
        project_id (int): 项目ID，用于查询项目配置信息
        questionnaire_ids (List[int]): 问卷ID集合，用于确定数据范围
        product_code (Optional[str]): 产品编码，用于路由到特定计算逻辑
        project_code (Optional[str]): 项目编码，用于路由到特定计算逻辑
        region_code (Optional[str]): 区域编码，用于路由到特定计算逻辑
        education (Optional[str]): 学历筛选条件，可选值：本科毕业生、专科毕业生、硕士研究生、博士研究生
        
    ## 示例
    ### 输入示例1：不指定学历（返回所有学历明细）
    ```json
    {
        "project_id": 5895,
        "questionnaire_ids": [11158, 11159]
    }
    ```
    
    ### 输出示例1：所有学历明细
    ```json
    {
        "success": true,
        "message": "ok",
        "code": 0,
        "result": [
            {
                "group": "本科毕业生",
                "data": [
                    {
                        "province": "四川省",
                        "count": 500,
                        "percentage": 0.5
                    },
                    {
                        "province": "广东省",
                        "count": 300,
                        "percentage": 0.3
                    },
                    {
                        "province": "浙江省",
                        "count": 200,
                        "percentage": 0.2
                    }
                ],
                "total": 1000
            },
            {
                "group": "专科毕业生",
                "data": [
                    {
                        "province": "四川省",
                        "count": 200,
                        "percentage": 0.4
                    },
                    {
                        "province": "广东省",
                        "count": 150,
                        "percentage": 0.3
                    },
                    {
                        "province": "浙江省",
                        "count": 150,
                        "percentage": 0.3
                    }
                ],
                "total": 500
            },
            {
                "group": "总体",
                "data": [
                    {
                        "province": "四川省",
                        "count": 1200,
                        "percentage": 0.48
                    },
                    {
                        "province": "广东省",
                        "count": 800,
                        "percentage": 0.32
                    },
                    {
                        "province": "浙江省",
                        "count": 500,
                        "percentage": 0.2
                    }
                ],
                "total": 2500
            }
        ]
    }
    ```
    
    ### 输入示例2：指定学历
    ```json
    {
        "project_id": 5895,
        "questionnaire_ids": [11158, 11159],
        "education": "本科毕业生"
    }
    ```
    
    ### 输出示例2：指定学历数据
    ```json
    {
        "success": true,
        "message": "ok",
        "code": 0,
        "result": [
            {
                "group": "本科毕业生",
                "data": [
                    {
                        "province": "四川省",
                        "count": 500,
                        "percentage": 0.5
                    },
                    {
                        "province": "广东省",
                        "count": 300,
                        "percentage": 0.3
                    },
                    {
                        "province": "浙江省",
                        "count": 200,
                        "percentage": 0.2
                    }
                ],
                "total": 1000
            },
            {
                "group": "总体",
                "data": [
                    {
                        "province": "四川省",
                        "count": 1200,
                        "percentage": 0.48
                    },
                    {
                        "province": "广东省",
                        "count": 800,
                        "percentage": 0.32
                    },
                    {
                        "province": "浙江省",
                        "count": 500,
                        "percentage": 0.2
                    }
                ],
                "total": 2500
            }
        ]
    }
    ```
    """
    logger.info(f"开始计算指标: 就业省分布, 项目ID: {project_id}")
    
    try:
        db = MySQLUtil()  

        # 1. 查询项目配置信息
        project_sql = """
        SELECT client_code, item_year 
        FROM client_item 
        WHERE id = %s
        """
        project_info = db.fetchone(project_sql, (project_id,))
        if not project_info:
            raise ValueError(f"未找到项目ID={project_id}的配置信息")

        client_code = project_info['client_code']
        item_year = project_info['item_year']
        
        logger.info(f"项目配置: client_code={client_code}, item_year={item_year}")

        # 2. 计算 shard_tb_key
        shard_tb_key = re.sub(r'^[A-Za-z]*0*', '', client_code)
        logger.info(f"计算得到 shard_tb_key: {shard_tb_key}")

        # 3. 构建学生表名
        student_table = f"dim_client_target_baseinfo_student_calc_{item_year}"
        
        # 4. 处理学历参数（空字符串转为None）
        if education == '':
            education = None
        
        # 5. 构建学历筛选条件
        education_condition = ""
        if education:
            valid_educations = ["本科毕业生", "专科毕业生", "硕士研究生", "博士研究生"]
            if education not in valid_educations:
                raise ValueError(f"无效的学历参数: {education}, 必须是以下值之一: {valid_educations}")
            education_condition = f"AND education = '{education}'"
        
        logger.info(f"学历筛选: {education if education else '不限制，返回所有学历明细'}")
        
        # 6. 定义查询函数：查询指定学历的就业省份分布
        def query_province_distribution(edu_condition: str, group_name: str) -> Dict[str, Any]:
            """查询指定学历的就业省份分布"""
            # 先查询总就业人数（用于计算占比）
            total_sql = f"""
            SELECT COUNT(*) as total_count
            FROM {student_table}
            WHERE shard_tb_key = %s
            {edu_condition}
            AND job_province != ''
            """
            total_result = db.fetchone(total_sql, (shard_tb_key,))
            total_count = total_result['total_count'] if total_result else 0
            
            if total_count == 0:
                logger.warning(f"分组 '{group_name}': 未找到有效的就业省份数据")
                return {
                    "group": group_name,
                    "data": [],
                    "total": 0
                }
            
            # 查询各省份分布
            sql = f"""
            SELECT
                job_province AS province,
                COUNT(*) AS count
            FROM
                {student_table}
            WHERE
                shard_tb_key = %s
                {edu_condition}
                AND job_province != ''
            GROUP BY
                job_province
            ORDER BY
                count DESC
            """
            
            results = db.fetchall(sql, (shard_tb_key,))
            
            if not results:
                logger.warning(f"分组 '{group_name}': 未找到有效的就业省份数据")
                return {
                    "group": group_name,
                    "data": [],
                    "total": 0
                }
            
            # 格式化结果
            data = []
            for row in results:
                count = int(row["count"])
                percentage = round(count / total_count, 4) if total_count > 0 else 0
                data.append({
                    "province": row["province"],
                    "count": count,
                    "percentage": percentage
                })
            
            # 确保按人数降序排序（双重保障）
            data.sort(key=lambda x: x['count'], reverse=True)
            
            return {
                "group": group_name,
                "data": data,
                "total": total_count
            }
        
        # 7. 计算各学历和总体数据
        result_data = []
        
        # 7.1 如果指定了学历，只返回该学历的数据
        if education:
            logger.info(f"开始计算学历 '{education}' 的就业省分布")
            edu_result = query_province_distribution(f"AND education = '{education}'", education)
            if edu_result["total"] > 0:
                result_data.append(edu_result)
        else:
            # 7.2 如果没有指定学历，返回所有学历的明细数据
            logger.info("开始计算所有学历的就业省分布明细")
            education_list = ["本科毕业生", "专科毕业生", "硕士研究生", "博士研究生"]
            
            for edu in education_list:
                logger.info(f"计算学历 '{edu}' 的就业省分布")
                edu_result = query_province_distribution(f"AND education = '{edu}'", edu)
                # 只添加有数据的学历
                if edu_result["total"] > 0:
                    result_data.append(edu_result)
        
        # 7.3 计算总体数据（必须包含）
        logger.info("开始计算总体的就业省分布")
        overall_result = query_province_distribution("", "总体")
        if overall_result["total"] > 0:
            result_data.append(overall_result)
        
        if not result_data:
            raise ValueError("未找到任何有效的就业省份数据")
        
        # 8. 对结果进行排序：总体放在最后，其他按学历顺序
        # 定义学历顺序
        education_order = {
            "本科毕业生": 1,
            "专科毕业生": 2,
            "硕士研究生": 3,
            "博士研究生": 4,
            "总体": 999  # 总体放在最后
        }
        
        result_data.sort(key=lambda x: (
            education_order.get(x.get('group', ''), 999),  # 按学历顺序
            -x.get('total', 0)  # 同学历内按总人数降序
        ))
        
        logger.info(f"指标 '就业省分布' 计算成功，返回 {len(result_data)} 条记录")
        return {
            "success": True,
            "message": "ok",
            "code": 0,
            "result": result_data
        }

    except Exception as e:
        logger.error(f"计算指标 '就业省分布' 时发生错误: {str(e)}", exc_info=True)
        return {
            "success": False,
            "message": f"数据获取失败: 就业省分布",
            "code": 500,
            "error": str(e)
        }